
GO

alter table dbo.HRutaDet
add 
	Conformar int,
	FechaC datetime,
	usuarioC int,
	ValDeclarado decimal(8,2),
	Valorizar int,
	FechaV datetime,
	usuarioV int,
	CTroncal decimal(8,2),
	CEnRe decimal(8,2),
	CRedes decimal(8,2),
	COtros decimal(8,2),
	TCostos decimal(8,2),
	FFlete decimal(8,2),
	FSegPor decimal(3,2),
	FSeguro decimal(8,2),
	FEnRe decimal(8,2),
	FCustodia decimal(8,2),
	FOtros decimal(8,2),
	TFactura decimal(8,2)

GO

update dbo.HRutaDet
set
	Conformar		=1,
	FechaC			='01/01/1801',
	usuarioC		=0,
	ValDeclarado	=0,
	Valorizar		=1,
	FechaV			='01/01/1801',
	usuarioV		=0,
	CTroncal		=0,
	CEnRe			=0,
	CRedes			=0,
	COtros			=0,
	TCostos			=0,
	FFlete			=0,
	FSegPor			=0,
	FSeguro			=0,
	FEnRe			=0,
	FCustodia		=0,
	FOtros			=0,
	TFactura		=0
	
GO

alter table dbo.HRutaDet
alter column Conformar int not null

GO

alter table dbo.HRutaDet
alter column FechaC datetime not null

GO
	
alter table dbo.HRutaDet
alter column usuarioC int not null

GO
	
alter table dbo.HRutaDet
alter column ValDeclarado decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column Valorizar int not null

GO
	
alter table dbo.HRutaDet
alter column FechaV datetime not null

GO
	
alter table dbo.HRutaDet
alter column usuarioV int not null

GO
	
alter table dbo.HRutaDet
alter column CTroncal decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column CEnRe decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column CRedes decimal(8,2) not null

GO

alter table dbo.HRutaDet
alter column COtros decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column TCostos decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column FFlete decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column FSegPor decimal(3,2) not null

GO
	
alter table dbo.HRutaDet
alter column FSeguro decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column FEnRe decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column FCustodia decimal(8,2) not null

GO

alter table dbo.HRutaDet
alter column FOtros decimal(8,2) not null

GO
	
alter table dbo.HRutaDet
alter column TFactura decimal(8,2) not null
	
GO

alter table dbo.HRutaDet
add 
	IdRubro int,
	IdSubRubro int,
	IdArticulo int,
	Articulo nvarchar(30),
	Cantidad decimal (8,2),
	Lote float
	
GO

update dbo.HRutaDet
set
	IdRubro		=0,
	IdSubRubro	=0,
	IdArticulo	=0,
	Articulo	='',
	Cantidad	=0,
	Lote		=0	
	
GO

alter table dbo.HRutaDet
alter column IdRubro int not null

GO

alter table dbo.HRutaDet
alter column IdSubRubro	int not null

GO
	
alter table dbo.HRutaDet
alter column IdArticulo	int not null

GO
	
alter table dbo.HRutaDet
alter column Articulo nvarchar(30) not null

GO
	
alter table dbo.HRutaDet
alter column Cantidad decimal (8,2) not null

GO

alter table dbo.HRutaDet
alter column Lote float not null

GO	
	
alter table dbo.HRutaDet
add M3 int

GO

update dbo.HRutaDet
set M3 = 0

GO

alter table dbo.HRutaDet
alter column M3 int not null 

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		25/07/2008
-- Descripcion: 	GetAll HRutaDet de un IdHRuta
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[HRutaDet_GetAllByPagina]
		@IdHRuta		float,
		@Pagina			int
AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY
SELECT 
	IdHRuta,
	Pagina,
	Item,
	Origen,
	IdCliente,
	Cliente,
	Remito,
	Embalaje,
	Cobro,
	TipoEntrega,
	Destino,
	Destinatario,
	Kilos,
	Bultos,
	PorCobrar,
	Entregar,
	FechaE,
	usuarioE,
	Imputar,
	FechaI,
	IdRemito,
	usuarioI,
	Conformar,
	FechaC,
	usuarioC,
	Valorizar,
	FechaV,
	usuarioV,
	IdRubro,
	IdSubRubro,
	IdArticulo,
	Articulo,
	Cantidad,
	Lote

	FROM HRutaDet 
	WHERE 
			IdHRuta		= @IdHRuta AND
			Pagina		= @Pagina
			
	ORDER BY
			Item	
END

-- exec HRutaDet_GetAllByPagina 1,1

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		23/08/2010
-- Descripcion: 	INSERT en tabla HRutaDet
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[HRutaDet_Insert]

		@IdHRuta		float,
		@Pagina			int,
		@Item			int,
		@Origen			nvarchar(30),
		@IdCliente		float,
		@Cliente		nvarchar(50),
		@Remito			numeric(10,0),
		@Embalaje		nvarchar(10),
		@Cobro			nvarchar(10),
		@TipoEntrega	nvarchar(15),
		@Destino		nvarchar(30),
		@Destinatario   nvarchar(50),
		@Kilos			decimal(8,2),
		@Bultos			decimal(8,2),
		@PorCobrar		decimal(8,2),
		@Entregar		int,
		@FechaE			datetime,
		@usuarioE		int,
		@Imputar		int,
		@FechaI			datetime,
		@IdRemito		float,
		@usuarioI		int,
		@Conformar		int,
        @FechaC			datetime,
        @usuarioC		int,
        @ValDeclarado	decimal(8,2),
        @Valorizar		int,
        @FechaV			datetime,
        @usuarioV		int,
        @CTroncal		decimal(8,2),
        @CEnRe			decimal(8,2),
        @CRedes			decimal(8,2),
        @COtros			decimal(8,2),
        @TCostos		decimal(8,2),
        @FFlete			decimal(8,2),
        @FSegPor		decimal(3,2),
        @FSeguro		decimal(8,2),
        @FEnRe			decimal(8,2),
        @FCustodia		decimal(8,2),
        @FOtros			decimal(8,2),
        @TFactura		decimal(8,2),
        @IdRubro		int,
        @IdSubRubro		int,
        @IdArticulo		int,
        @Articulo		nvarchar(30),
        @Cantidad		decimal(8,2),
        @M3				int,
        @Lote			float
		
AS
BEGIN
	SET NOCOUNT ON

	SET DATEFORMAT DMY

	INSERT INTO HRutaDet (
		IdHRuta,
		Pagina,
		Item,
		Origen,
		IdCliente,
		Cliente,
		Remito,
		Embalaje,
		Cobro,
		TipoEntrega,
		Destino,
		Destinatario,
		Kilos,
		Bultos,
		PorCobrar,
		Entregar,
		FechaE,
		usuarioE,
		Imputar,
		FechaI,
		IdRemito,
		usuarioI,
		Conformar,
        FechaC,
        usuarioC,
        ValDeclarado,
        Valorizar,
        FechaV,
        usuarioV,
        CTroncal,
        CEnRe,
        CRedes,
        COtros,
        TCostos,
        FFlete,
        FSegPor,
        FSeguro,
        FEnRe,
        FCustodia,
        FOtros,
        TFactura,
        IdRubro,
        IdSubRubro,
        IdArticulo,
        Articulo,
        Cantidad,
        M3,
        Lote 	

	) VALUES (
		@IdHRuta,
		@Pagina,
		@Item,
		@Origen,
		@IdCliente,
		@Cliente,
		@Remito,
		@Embalaje,
		@Cobro,
		@TipoEntrega,
		@Destino,
		@Destinatario,
		@Kilos,
		@Bultos,
		@PorCobrar,
		@Entregar,
		@FechaE,
		@usuarioE,
		@Imputar,
		@FechaI,
		@IdRemito,
		@usuarioI,
		@Conformar,
        @FechaC,
        @usuarioC,
        @ValDeclarado,
        @Valorizar,
        @FechaV,
        @usuarioV,
        @CTroncal,
        @CEnRe,
        @CRedes,
        @COtros,
        @TCostos,
        @FFlete,
        @FSegPor,
        @FSeguro,
        @FEnRe,
        @FCustodia,
        @FOtros,
        @TFactura,
        @IdRubro,
        @IdSubRubro,
        @IdArticulo,
        @Articulo,
        @Cantidad,
        @M3,
        @Lote 	
	)
END

GO

alter table dbo.PLogistica
add DiasLotes int

GO

update dbo.PLogistica
set DiasLotes=0

GO

alter table dbo.PLogistica
alter column DiasLotes int not null 

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		20/08/2010
-- Descripcion: 	Get PLogistica
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[PLogistica_Get]
AS
BEGIN
	SET NOCOUNT ON
SELECT 
		Id,
		Dias,
		Km,
		Titulo,
		DiasLotes  
	FROM PLogistica 
	WHERE
		Id = 1
END

-- exec [dbo].[PLogistica_Get]

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		20/08/2010
-- Descripcion: 	UPDATE en tabla PLogistica
--------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[PLogistica_Update]
	-- Lista de Parmetros
		@Dias		int,
		@Km 		numeric(7,0),
		@Titulo		nvarchar(50),
		@DiasLotes	int
AS
UPDATE PLogistica 
	SET
		Dias		= @Dias,
		Km			= @Km,
		Titulo		= @Titulo,
		DiasLotes	= @DiasLotes 
		
WHERE
		Id		= 1

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo\cbendayan
-- Creacion:		22/03/2010
-- Descripcion: 	Search en tabla Lotes
-------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Lotes_Search]
	-- Lista de Parmetros
	@Id_Rubro			int = null,
	@Id_SubRubro		int = null,			
	@Id_Articulo		int = null,
	@Lote				float = null,
	@Estado				int = null,
	@FechaVto			datetime = null
	

AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY

	----------------------------------------
	-- Definimos Variables
	----------------------------------------
	DECLARE @sql        nvarchar(4000)
	DECLARE @parametros nvarchar(4000)

	SET @sql = N'	SELECT 
					Id_Rubro,
					Id_SubRubro,			
					Id_Articulo,
					Articulo,
					Lote,
					Estado,
					FechaVto,
					Saldo,
					Id_Usuario
					FROM Lotes WITH (NOLOCK) WHERE 1 = 1 '

	IF (@Id_Rubro IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_Rubro = @Id_Rubro '
	END

	IF (@Id_SubRubro IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_SubRubro = @Id_SubRubro'
	END

	IF (@Id_Articulo IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Id_Articulo = @Id_Articulo '
	END

	IF (@Lote IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Lote = @Lote '
	END

	IF (@Estado IS NOT NULL)
	BEGIN			
			SET @sql = @sql + ' AND Estado = @Estado '
	END

	IF (@FechaVto IS NOT NULL)	
	BEGIN
			SET @sql = @sql + ' AND FechaVto > @FechaVto '
	END

	BEGIN
			SET @sql = @sql + ' ORDER BY Id_Rubro,Id_SubRubro,Id_Articulo,Lote '
	END
	
	SELECT @parametros =	'@Id_Rubro			int,
							@Id_SubRubro		int,			
							@Id_Articulo		int,
							@Lote				float,
							@Estado				int,
							@FechaVto			datetime '

	EXEC sp_executesql @sql, @parametros,
							@Id_Rubro,
							@Id_SubRubro,			
							@Id_Articulo,
							@Lote,
							@Estado,
							@FechaVto 
END

-- exec Lotes_Search 1,1,20,null,null,null

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		03/10/2010
-- Descripcion: 	GetAll RemitosDet de un Lote
--------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[RemitosDet_GetAllByLote]
		@Id_Rubro		int,
		@Id_SubRubro	int,
		@Id_Articulo	int,
		@Lote			float
AS
BEGIN
	SET NOCOUNT ON
	SET DATEFORMAT DMY
SELECT 
		Id_Remitos,
		Item,
		Id_Rubro,
		Rubro,
		Id_SubRubro,
		SubRubro,
		Id_Articulo,
		Articulo,
		Lote,
		FechaVto,
		Kilos,
		Id_Usuario 

	FROM RemitosDet 
	WHERE 
		Id_Rubro		=	@Id_Rubro		AND
		Id_SubRubro		=	@Id_SubRubro	AND
		Id_Articulo		=	@Id_Articulo	AND
		Lote			=	@Lote 
	ORDER BY
		Id_Rubro,
		Id_SubRubro,
		Id_Articulo,
		Lote,
		Item 	
END

-- exec RemitosDet_GetAll 1,1,20,7

GO

---------------------------------------------------------------------------------------------------
-------------------        Nueva Tabla SuperMenu

GO

CREATE TABLE [dbo].[SuperMenu](
	[Id_Usuario] [int] NOT NULL,
	[Menu] [int] NOT NULL,
	[IdTarea] [int] NOT NULL,
	[Tarea] [nvarchar](20) NOT NULL,
	[Ver] [int] NOT NULL,
 CONSTRAINT [PK_SuperMenu] PRIMARY KEY CLUSTERED 
(
	[Id_Usuario] ASC,
	[Menu] ASC,
	[IdTarea] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/cbendayan
-- Creacion:		04/10/2008
-- Descripcion: 	GetAll Tareas de Menu de un Usuario
--------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SuperMenu_GetAll]
	@Id_Usuario  int		
AS
BEGIN
	SET NOCOUNT ON
SELECT 
		Id_Usuario,
		Menu,
		IdTarea,
		Tarea,
		Ver	

	FROM SuperMenu
	WHERE 
			Id_Usuario	= @Id_Usuario	AND
			VER			= 1		
	ORDER BY 
			Menu, IdTarea
END

-- exec SuperMenu_GetALL 1

GO

--------------------------------------------------------------------------------------------
-- Autor:			WeCo/CBendayan
-- Creacion:		04/10/2010
-- Descripcion: 	UPDATE en tabla SuperMenu
--------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SuperMenu_Update]

	-- Lista de Parmetros
		@Id_Usuario	int,
		@Menu		int,
		@IdTarea	int,
		@Ver		int 
AS
UPDATE SuperMenu
	SET
		Ver	= @Ver
WHERE
		Id_Usuario	= @Id_Usuario	AND
		Menu		= @Menu			AND
		IdTarea		= @IdTarea	 
		
GO
